'''
分数数据提交系统主文件
'''
# 导入系统模块
import pyodbc
import os
import re
import json
from datetime import datetime
from flask import Flask, render_template, redirect, url_for, request
from flask_wtf import FlaskForm
from wtforms import StringField, BooleanField, SubmitField, TextAreaField, RadioField
from wtforms.validators import DataRequired, Length
from flask_wtf.file import FileField, FileRequired, FileAllowed
import pandas as pd
# 导入自制模块
from csvtosql2 import csv_to_sql
from infostosql2 import infos_to_sql, delete_info
from weblog import get_logger

# 初始化日志功能，启动日志记录
logger = get_logger(__name__)
# 调试模式可以使用debug方法
# logger.info("....")

# Flask实例化，加密Cookie
app = Flask(__name__)
app.secret_key = os.getenv('SECRET_KEY', 'secret string')
# 删除Jinja2语句中的空格和制表符
app.jinja_env.trim_blocks = True
app.jinja_env.lstrip_blocks = True
# 上传附件最大10Mb上限
app.config['MAX_CONTENT_LENGTH'] = 10 * 1024 * 1024

# 分光机设备IP地址列表，后续根据ip判定设备编号，192.168.3.247为服务器地址，其他计算机需要访问需要加上IP 15号192.168.20.108->192.168.20.72 23号：192.168.20.105
ipLists = ['192.168.3.247',
           '192.168.30.101', '192.168.30.57', '192.168.30.148', '192.168.30.198', '192.168.30.134',
           '192.168.30.195', '192.168.30.16', '192.168.30.76', '192.168.30.151', '192.168.30.10',
           '192.168.30.66', '192.168.30.83', '192.168.30.196', '192.168.20.219', '192.168.20.72',
           '192.168.30.108', '192.168.30.3', '192.168.30.179', '192.168.60.98', '192.168.30.45',
           '192.168.30.72', '192.168.60.183', '192.168.20.105']


# 定义输入界面表单类，设置相关验证要求
class InputForm(FlaskForm):
    mo_id = StringField('工单号', validators=[DataRequired(), Length(13, 13)])
    user_id = StringField('工号', validators=[DataRequired(), Length(5, 5)])
    rework = BooleanField(label='重工')
    sampling = BooleanField(label='抽测')
    concession = BooleanField(label='特采')
    inventory = BooleanField(label='库存')
    one_to_one = BooleanField(label='1:1')
    check_pot = BooleanField(label='点检')
    double_meter = BooleanField(label='对测')
    pulse = BooleanField(label='脉冲')
    notes = TextAreaField('备注')
    excel_data = FileField('附件', validators=[FileRequired(), FileAllowed(['csv', 'xls'])])
    submit = SubmitField('提交')

    # 定义文件修改函数，新文件名为 工单号+批号+扩展名


def alter_filename(filename, mo_id, lot_id):
    ext = os.path.splitext(filename)[1]
    new_filename = mo_id + '-' + str(lot_id) + ext
    return new_filename


# 注册/路由和upload视图函数
@app.route('/', methods=['GET', 'POST'])
def upload():
    # 数据库连接
    # conn = pyodbc.connect(
    #     'DRIVER={ODBC Driver 17 for SQL Server};SERVER=192.168.3.236;DATABASE=SortingDB;UID=qatest;PWD=qatest',
    #     fast_executemany=True)

    conn = pyodbc.connect(
        'DRIVER={ODBC Driver 17 for SQL Server};SERVER=172.18.65.31;DATABASE=SortingDB;UID=peapp;PWD=peapp1',
        fast_executemany=True)
    form = InputForm()
    # 根据IP地址判定并添加MachineID，服务器ID为0，非分光机台为100
    ip = request.remote_addr
    # print(ip)
    if ip in ip_list:
        machine_name = json_info[ip]['MachineName']
        machine_id = json_info[ip]['MachineID']
    elif ip == '127.0.0.1':
        machine_name = '本地机'
        machine_id = 3
        # logger.info(f'{machine_id}号机台-进入系统')
    except ValueError:
        machine_id = 100
    # 非授权计算机IP将被重新定向拒绝访问页面
    if machine_id == 100:
        logger.warning(f'{ip}拒绝访问，如需访问添加进系统允许IP')
        return redirect(url_for('reject'))

    # print(machine_id)
    # 根据年月份自动创建上传文件目录
    uploadsDir = os.path.join('uploads', str(datetime.now().year), str(datetime.now().month))
    if os.path.exists(uploadsDir):
        pass
    else:
        os.makedirs(uploadsDir)
    app.config['UPLOAD_PATH'] = os.path.join(app.root_path, uploadsDir)
    # print(uploadsDir,type(uploadsDir))
    if form.validate_on_submit():
        MoID = form.mo_id.data
        if not re.match(r"\d{4}-\d{8}", MoID):
            return '<p>检查工单号!!!</p><br><a href="/">返回<a>'

        # 获取文件名
        filename = request.files.getlist('excel_data')[0].filename
        logger.info(f'文件名：{filename}\t工单号：{MoID}')
        if not filename.startswith(f'{MoID}'):
            logger.warning(f'{machine_id}号机台，使用不对应工单号文件上传！')
            return '<p>工单号与文件名称不对应，请检查!!!</p><br><a href="/">返回<a>'
        # 获取上传文件信息
        f = form.excel_data.data
        # # 将表单相关信息存为列表，方便数据处理,依次是工单号，工号，重工，抽测，特采，库存，备注，上传文件夹，原文件名
        form_infos = [form.mo_id.data, machine_id, form.user_id.data, int(form.rework.data),
                      int(form.sampling.data), int(form.concession.data), int(form.inventory.data), form.notes.data,
                      uploadsDir, f.filename]
        # 调用infos_to_sql方法将表单基本信息写入数据库，返回lot_id
        lot_id = infos_to_sql(form_infos, conn, logger)
        # 修改文件名，设置上传文件路径
        filename = alter_filename(f.filename, form.mo_id.data, lot_id)
        path = os.path.join(app.config['UPLOAD_PATH'], filename)
        # print(path)
        # 根据路径保存文件
        f.save(path)
        # 调用csv_to_sql方法将数据写入数据库
        result, string = csv_to_sql(csv_path=path, machine_id=machine_id, moID=form.mo_id.data, lotID=lot_id,
                                    pulse=form.pulse.data, flag=form.one_to_one.data, is_check=form.check_pot.data,
                                    double_meter=form.double_meter.data, logger=logger, conn=conn, filename=f.filename)
        if not result == 0:
            delete_info(moid=form.mo_id.data, lotid=lot_id, conn=conn, logger=logger)
            conn.close()
            if result == 1:
                return '<p>上传文件读取错误!!!</p><br><a href="/">返回<a>'
            elif result == 2:
                return '<p>数据库存储错误!!!</p><br><a href="/">返回<a>'
            elif result == 3:
                return '<p>上传数据不符合冷热态要求!!!</p><br><a href="/">返回<a>'
            elif result == 4:
                return '<p>上传数据不合规范，关键参数为空，执行删除!!!</p><br><a href="/">返回<a>'
            elif result == 5:
                return '<p>ITEM_CODE_VALUE表中未找到质量标准!!!使用质量标准：9</p><br><a href="/">返回<a>'
            elif result == 6:
                return f'<p>请使用正确的质量标准命名，质量标准为{string}</p><br><a href="/">返回<a>'
            elif result == 7:
                return '<p>上传数据长度不符合规范，数据长度应在文件名数量的±10%内!!!</p><br><a href="/">返回<a>'
            elif result == 8:
                return '<p>未匹配到数量,请检查文件命名！！</p><br><a href="/">返回<a>'
            elif result == 9:
                return '<p>数据已上传，未能上传到数据库</p><br><a href="/">返回<a>'
        else:
            logger.info(f'{machine_id}号机台，结束上传！')
            conn.close()
            # 成功上传的不记录在服务器
            os.remove(path)
            return redirect(url_for('result'))
    return render_template('upload.html', form=form)


# 注册/result路由和result视图函数
@app.route('/result', methods=['GET'])
def result():
    return render_template('result.html')


# 注册/reject路由和reject视图函数
@app.route('/reject', methods=['GET'])
def reject():
    return render_template('reject.html')


# 主程序入口
if __name__ == '__main__':
    app.run()
